sp_hexadecimal和sp_help_revlogin脚本的版本有点老，有人做了更新，因此记录一下。

相关连接：

导出sqlserver数据库中的登录用户和密码信息 - abce - 博客园 (cnblogs.com)

Stop using sp_hexadecimal & sp_help_revlogin - Andy M Mallon - AM² (am2.co)

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'V' AND object_id = object_id('dbo.ServerLogins')) EXEC ('CREATE VIEW dbo.ServerLogins AS SELECT Result = ''This is a stub'';' )GOALTER VIEW dbo.ServerLogins/*************************************************************************************************AUTHOR: Andy MallonCREATED: 20210627 View to get basic info for logins, simplifying/flattening DMVs, to make applying additional criteria/filters from automation & troubleshooting easierEXAMPLES:* All logins that can log in (enabled + have CONNECT SQL), modified in the last 7 days: SELECT LoginName, DateModified, CreateSql FROM dbo.ServerLogins WHERE CanLogIn = 1 AND DateModified >= DATEADD(DAY, -7, GETUTCDATE())************************************************************************************************** This code is licensed as part of Andy Mallon's DBA Database. https://github.com/amtwo/dba-database/blob/master/LICENSE ©2014-2020 ● Andy Mallon ● am2.co*************************************************************************************************/AS SELECT LoginSid = p.sid, LoginName = p.name, LoginType = p.type_desc, DefaultDatabase = p.default_database_name, VarbinaryPasswordHash = sl.password_hash, IsPolicyChecked = IIF(sl.is_policy_checked=1,1,0), IsExpiratiOnChecked= IIF(sl.is_expiration_checked=1,1,0), IsEnabled = IIF(p.is_disabled = 0,1,0), CanLogIn = IIF(perm.state IN ('G','W'),1,0), CreateSql = CASE WHEN p.type IN ('U','G') THEN CONCAT(N'CREATE LOGIN ', QUOTENAME(p.name), N' FROM WINDOWS', N' WITH DEFAULT_DATABASE = ', QUOTENAME(p.default_database_name), N';' ) WHEN p.type = 'S' THEN CONCAT(N'CREATE LOGIN ', QUOTENAME(p.name), N' WITH PASSWORD = ', CONVERT(varchar(514), sl.password_hash, 1), N' HASHED, SID = ', CONVERT(varchar(514), p.sid, 1), N', DEFAULT_DATABASE = ', QUOTENAME(p.default_database_name), N', CHECK_POLICY = ', IIF(sl.is_policy_checked=1,N'ON','OFF'), N', CHECK_EXPIRATION = ', IIF(sl.is_expiration_checked=1,N'ON','OFF'), N';' ) END, EnableSql = CONCAT(N'ALTER LOGIN ', QUOTENAME(p.name), ' ', IIF(p.is_disabled = 1,' DISABLE',' ENABLE'), N';' ), DateCreated = p.create_date, DateModified = p.modify_date FROM sys.server_principals AS p LEFT JOIN sys.sql_logins AS sl ON p.name = sl.name --Left join here to check to determine if the login is enabled & has connect SQL LEFT JOIN sys.server_permissions AS perm ON perm.grantee_principal_id = p.principal_id AND perm.type = 'COSQ' AND perm.state IN ('G','W') AND p.is_disabled = 0 WHERE p.type IN ('S','U','G') AND p.name N'sa' AND p.name NOT LIKE N'##%##';GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'V' AND object_id = object_id('dbo.ServerLoginPermissions')) EXEC ('CREATE VIEW dbo.ServerLoginPermissions AS SELECT Result = ''This is a stub'';' )GOALTER VIEW dbo.ServerLoginPermissions/*************************************************************************************************AUTHOR: Andy MallonCREATED: 20210627 View to get basic info for server-level permissions, simplifying/flattening DMVs, to make applying additional criteria/filters from automation & troubleshooting easierEXAMPLES:* Get GRANT/DENY commands for permissions on all enabled users SELECT p.LoginName, p.PermissionSql FROM dbo.ServerLoginPermissions AS p JOIN dbo.ServerLogins AS l ON l.LoginSid = p.LoginSid WHERE l.IsEnabled = 1;************************************************************************************************** This code is licensed as part of Andy Mallon's DBA Database. https://github.com/amtwo/dba-database/blob/master/LICENSE ©2014-2020 ● Andy Mallon ● am2.co*************************************************************************************************/AS SELECT LoginSid = p.sid, LoginName = p.name, LoginType = p.type_desc, DefaultDatabase = p.default_database_name, LoginIsEnabled = IIF(p.is_disabled = 0,1,0), CanLogIn = COALESCE((SELECT TOP 1 1 FROM sys.server_permissions AS cosq WHERE cosq.grantee_principal_id = p.principal_id AND cosq.type = 'COSQ' AND cosq.state IN ('G','W') AND p.is_disabled = 0 ), 0), PermissiOnType= perm.type, PermissiOnState= perm.state, PermissiOnSql= CONCAT(perm.state_desc, N' ', perm.permission_name, N' TO ', QUOTENAME(p.name) COLLATE Latin1_General_CI_AS_KS_WS, N';' ), DateLoginCreated = p.create_date, DateLoginModified = p.modify_date FROM sys.server_principals AS p JOIN sys.server_permissions AS perm ON perm.grantee_principal_id = p.principal_id WHERE p.type IN ('S','U','G') AND p.name N'sa' AND p.name NOT LIKE N'##%##';GO

本文来自博客园，作者：abce，转载请注明原文链接：https://www.cnblogs.com/abclife/p/16992572.html